package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBEquipment implements IFDBEquipment{
	
	private Connection con;
	public DBEquipment() { con = DBConnection.getInstance().getDBcon();	}
	
	// get all Equipment	
    public ArrayList<Equipment> getAllEquipment() {
    	return miscWhere("");
    }
    
    //get Equipment 
    public Equipment findequipmentID(int equipmentID) {
    	String wClause = "  equipmentID = " + equipmentID;
        return singleWhere(wClause);
    }
    
    //insert Equipment    
    public void insertEquipment(Equipment equipment) throws SQLException {
    	
        String query="INSERT INTO Equipment(equipmentID, etype, edescription)  VALUES("+
        equipment.getProductID()  + ",'"  +
        equipment.getetype()  + "','"  +
        equipment.getedescription() + "')";
                      
       System.out.println("insert : " + query);	
       try{ 
            Statement stmt = con.createStatement();
 	 	  	stmt.setQueryTimeout(5);
 	 	  	stmt.executeUpdate(query);
 	 	  	stmt.close();
 		}	
       
 	 	catch(SQLException ex){
 	 	  	System.out.println("Insert exception in clothing db: "+ex);
 	   }
    	
    }
    
    //update Equipment    
    public void updateEquipment (Equipment equipment) throws SQLException {
    	String query="UPDATE Equipment SET " +
    			"etype = '"+equipment.getetype()+"'" +
    			"edescription = '"+equipment.getedescription()+"'" +
    			"WHERE equipmentID = "+equipment.getProductID();
    	                      
    	       System.out.println("Update : " + query);	
    	       try{ 
    	            Statement stmt = con.createStatement();
    	 	 	  	stmt.setQueryTimeout(5);
    	 	 	  	stmt.executeUpdate(query);
    	 	 	  	stmt.close();
    	 		}	
    	       
    	 	 	catch(Exception ex){
    	 	 	  	System.out.println("Update exception in clothing db: "+ex);
    	 	   }
    	    }
    //Other methods
    //SingleWhere    
  	private Equipment singleWhere(String wClause)
  	{
  		ResultSet results;
  		Equipment equipmentObj = new Equipment();
  	    String query =  buildQuery(wClause);
          System.out.println(query);
          
  		try{ 
  	 		Statement stmt = con.createStatement();
  	 		stmt.setQueryTimeout(5);
  	 		results = stmt.executeQuery(query);
  	 		
  	 		if( results.next() ){
                              equipmentObj = buildEquipment(results);
                              //association is to be build
                              stmt.close();
  			}
  		
  		}//end try	
  	 	catch(Exception e){
  	 		System.out.println("Query exception: "+e);
  	 	}
  		return equipmentObj;
  	}
  	
  	//miscWhere
    private ArrayList<Equipment> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Equipment> list = new ArrayList<Equipment>();	
		
		String query =  buildQuery(wClause);
  
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		

			while( results.next() ){
		     	 Equipment equipmentObj = new Equipment();
			 equipmentObj = buildEquipment(results);	
                         list.add(equipmentObj);	
			}
			stmt.close();
		}
  		
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
  //method to build the query
    
  	private String buildQuery(String wClause)
  	{
  	    String query="SELECT * FROM Equipment";
  		
  		if (wClause.length()>0)
  			query=query+" WHERE "+ wClause;
  			
  		return query;
  	}
  	//method to build an Equipment object
  	private Equipment buildEquipment(ResultSet results)
        {   Equipment equipmentObj = new Equipment();
            try{    
            	equipmentObj.setProductID(results.getInt(1));
            	equipmentObj.setetype(results.getString(2));
            	equipmentObj.setedescription(results.getString(3));
                 
            }
           catch(Exception e)
           {
               System.out.println("error in building the equipment object");
           }
           return equipmentObj;
        }   
}